package lf.day0109.travel.impl;

import lf.day0109.travel.dao.TravelDao;
import lf.day0109.travel.entity.Route;
import lf.day0109.travel.entity.Spot;
import lf.day0109.travel.uilts.BaseDao;


import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class TravelDaoImpl extends BaseDao implements TravelDao {
    @Override
    public List<Spot> selectAll(Map<String, Object> map) {
        List<Spot> spotLists = new ArrayList<>();
        Spot spot = null;
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT SS_ID,SS_NAME,SS_DURATION,SS_CONTENT FROM T_SCENIC_SPOT ");
        sql.append(" WHERE 1 = 1 ");
        Object name = map.get("ssName");
        if ((!"".equals(name) & name != null) ){
            sql.append(" AND SS_NAME LIKE ? ");
            name = "%" + name + "%";
            sql.append(" order by SS_ID asc ");
            rs = super.executeQuery(sql.toString(),new Object[] {name});
        }else {
            sql.append(" order by SS_ID asc ");
            rs = super.executeQuery(sql.toString());
        }

        try {
            while(rs.next()){
                spot = new Spot();
                spot.setSsId(rs.getInt("SS_ID"));
                spot.setSsName(rs.getString("SS_NAME"));
                spot.setSsDuration(rs.getString("SS_DURATION"));
                spot.setSsContent(rs.getString("SS_CONTENT"));
                spotLists.add(spot);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            super.closeAllConn(conn,pstmt,rs);
        }
        return spotLists;
    }

    @Override
    public List<Route> selectRouteAll(Map<String, Object> map) {
        List<Route> routelist = new ArrayList<>();
        Route route = null;
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT TR_ID,TR_NAME,TR_TYPE,TR_PRICE,TR_PHONE,TR_USER,TO_CHAR(CREATE_TIME) FROM T_TOUR_ROUTE ");
        sql.append(" WHERE 1 = 1 ");
        Object id = map.get("routeId");
        Object name = map.get("routeName");
        if ((!"".equals(id) & id != null) & name == null) {
            sql.append(" AND TR_ID = ? ORDER BY TR_ID ");
            rs = super.executeQuery(sql.toString(), new Object[]{id});
        } else if ((!"".equals(name) & name != null) & id == null) {
            name = "%" + name + "%";
            sql.append(" AND TR_NAME LIKE ? ORDER BY TR_ID ");
            rs = super.executeQuery(sql.toString(), new Object[]{name});
        } else {
            sql.append(" ORDER BY TR_ID ");
            rs = super.executeQuery(sql.toString());
        }
        try {
            while (rs.next()) {
                route = new Route();
                route.setTrId(rs.getString("TR_ID"));
                route.setTrName(rs.getString("TR_NAME"));
                route.setType(rs.getString("TR_TYPE"));
                route.setPrice(rs.getString("TR_PRICE"));
                route.setPhone(rs.getString("TR_PHONE"));
                route.setUser(rs.getString("TR_USER"));
                route.setCreateTime(rs.getString("TO_CHAR(CREATE_TIME)"));
                routelist.add(route);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            super.closeAllConn(conn, pstmt, rs);
        }
        return routelist;
    }

    @Override
    public int addRoute(Route route) {
        int addRes =0;
        String sql =" INSERT INTO T_TOUR_ROUTE(TR_ID,TR_NAME,TR_TYPE,TR_PRICE,TR_PHONE,TR_USER,CREATE_TIME) " +
                " VALUES(SEQ_TOUR_ROUTE_TR_ID.NEXTVAL,?,1,?,?,?,SYSDATE) ";
        addRes = super.executeUpdate(sql,new Object[]{route.getTrName(),route.getPrice(),route.getPhone(),route.getUser()});
        return addRes;
    }


    @Override
    public int delete(Route route) {
        String sql = "DELETE FROM T_SCENIC_SPOT WHERE ID = ?";
        int delete = super.executeUpdate(sql,new Object[]{route.getTrId()});
        return delete;
    }
}
